﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DuMez.DAL
{
    public class SqlUtility
    {
        public static string strConnection = "";

        public static string strException = "";

        public SqlUtility()
        {

        }

        public static bool ExecuteNonQuery(string strSql)
        {
            bool result = true;
            SqlConnection conn = null;
            strException = "";
            try
            {
                conn = new SqlConnection(strConnection);
                using (SqlCommand cmd = new SqlCommand(strSql, conn))
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception exp)
            {
                strException = exp.ToString();
                result = false;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static bool ExecuteNonQuery(SqlCommand cmd)
        {
            bool result = true;
            SqlConnection conn = null;
            strException = "";
            try
            {
                conn = new SqlConnection(strConnection);
                cmd.Connection = conn;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                strException = exp.ToString();
                result = false;
            }
            finally
            {
                conn.Close();
                conn = null;
            }
            return result;
        }


        public static object ExecuteScalar(string strSql)
        {
            object obj;
            using (SqlConnection conn = new SqlConnection(strConnection))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(strSql, conn))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    obj = (object)dt.Rows[0][0];
                }
            }
            return obj;
        }

        public static DataTable ExecuteDataTable(string strSql)
        {
            DataTable dt;
            using (SqlConnection conn = new SqlConnection(strConnection))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(strSql, conn))
                {
                    dt = new DataTable();
                    da.Fill(dt);
                }

            }
            return dt;
        }

        public static DataRow ExecuteDataRow(string strSql)
        {
            DataTable dt;
            using (SqlConnection conn = new SqlConnection(strConnection))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(strSql, conn))
                {
                    dt = new DataTable();
                    da.Fill(dt);
                }

            }
            return dt.Rows[0];
        }

        public static DataRow ExecuteDataRow(SqlCommand cmd)
        {
            DataTable dt;
            using (SqlConnection conn = new SqlConnection(strConnection))
            {
                cmd.Connection = conn;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    dt = new DataTable();
                    da.Fill(dt);
                }

            }
            return dt.Rows[0];
        }

        public static DataTable ExecuteDataTable(SqlCommand cmd)
        {
            DataTable dt;
            using (SqlConnection conn = new SqlConnection(strConnection))
            {
                cmd.Connection = conn;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    dt = new DataTable();
                    da.Fill(dt);
                }

            }
            return dt;
        }

        public static DataSet ExecuteDataSet(string strSql)
        {
            DataSet result;
            using (SqlConnection conn = new SqlConnection(strConnection))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(strSql, conn))
                {
                    result = new DataSet();
                    da.Fill(result);
                }
            }
            return result;
        }
    }
}

